﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using DTO;
namespace DAO
{
    public class CTPhieuBanDAO
    {
        public string LayMaCTPBLonNhat() // Lay mã phieu ban lon nhat
        {
            string sql = "SELECT TOP 1, MACTPB FROM CTPHIEUBAN  ORDER BY MACTPB DESC";
            OleDbConnection conn = new OleDbConnection();
            conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader reader = cmd.ExecuteReader();
            CTPhieuBanDTO ctpbDTO = new CTPhieuBanDTO();
            while (reader.Read())
            {
                ctpbDTO.MaCTPB = reader.GetString(0);
            }
            conn.Close();
            return ctpbDTO.MaCTPB;
        }
        public string NextID(string lastID, string prefixID)
        {
            if (lastID == null)
            {
                return prefixID + "001";  // fixwidth default
            }
            int nextID = int.Parse(lastID.Remove(0, prefixID.Length)) + 1;
            int lengthNumerID = lastID.Length - prefixID.Length;
            string zeroNumber = "";
            for (int i = 1; i <= lengthNumerID; i++)
            {
                if (nextID < Math.Pow(10, i))
                {
                    for (int j = 1; j <= lengthNumerID - i; i++)
                    {
                        zeroNumber += "0";
                    }
                    return prefixID + zeroNumber + nextID.ToString();
                }
            }
            return prefixID + nextID;

        }
        public string TaoCTHDMoi()
        {
            string LastID = LayMaCTPBLonNhat();
            return NextID(LastID, "CTPB");
        }
        public int ThemCTPhieuBan(CTPhieuBanDTO ctpbDTO)
        {
            OleDbConnection conn = DataProvider.ConnectDB();
            string sql = "INSERT INTO CTPHIEUBAN(MaCTPB,MaBH,MaSP,MaKH,SLBan,DonGia,ThanhTien) ";
            sql = sql + " VALUES(@MaCTPB,@MaBH,@MaSP,@MaKH,@SLBan,@DonGia,@ThanhTien) ";
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbParameter para = cmd.Parameters.Add("@MaCTPB", OleDbType.VarChar);
            para.Value = ctpbDTO.MaCTPB;
            para = cmd.Parameters.Add("@MaBH", OleDbType.VarChar);
            para.Value = ctpbDTO.MaBH;
            para = cmd.Parameters.Add("@MaSP", OleDbType.VarChar);
            para.Value = ctpbDTO.MaSP;
            para = cmd.Parameters.Add("@MaKH", OleDbType.VarChar);
            para.Value = ctpbDTO.MaKH;
            para = cmd.Parameters.Add("@SLBan", OleDbType.Integer);
            para.Value = ctpbDTO.SLBan;
            para = cmd.Parameters.Add("@DonGia", OleDbType.Double);
            para.Value = ctpbDTO.DonGia;
            para = cmd.Parameters.Add("@ThanhTien", OleDbType.Double);
            para.Value = ctpbDTO.ThanhTien;
            int res = cmd.ExecuteNonQuery();
            conn.Close();
            if (res >= 0)
                return 1; // thành công
            return 0; // không thành công
        }
        public List<CTPhieuBanDTO> TheoDoiBanHang()
        {
            string sql = "SELECT  p.NgayBH,c.MaSP,Sum(c.SLBan) FROM CTPHIEUBAN c,PHIEUBAN p  WHERE c.MaBH=p.MaBH  GROUP BY p.NgayBH,c.MaSP";
            OleDbConnection conn = new OleDbConnection();
            conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader reader = cmd.ExecuteReader();
            List<CTPhieuBanDTO> DanhSach = new List<CTPhieuBanDTO>();

            while (reader.Read())
            {
                if (reader.IsDBNull(0) || reader.IsDBNull(1) || reader.IsDBNull(2))
                    DanhSach = null;
                else
                {

                    CTPhieuBanDTO ctpbDTO = new CTPhieuBanDTO();
                    ctpbDTO.NgayBanCT = reader.GetDateTime(0);
                    ctpbDTO.MaSP = reader.GetString(1);
                    ctpbDTO.TongSLBan = reader.GetDouble(2);
                    DanhSach.Add(ctpbDTO);
                }
            }
            conn.Close();
            return DanhSach;
        }
        public List<CTPhieuBanDTO> TheoDoiXuat(string MaBH)
        {
            string sql = "SELECT MaSP,SLBan,ThanhTien FROM CTPHIEUBAN  WHERE MaBH = '" + MaBH + "'";
            OleDbConnection conn = new OleDbConnection();
            conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader reader = cmd.ExecuteReader();
            List<CTPhieuBanDTO> DanhSach = new List<CTPhieuBanDTO>();

            while (reader.Read())
            {
                if (reader.IsDBNull(0) || reader.IsDBNull(1) || reader.IsDBNull(2))
                    DanhSach = null;
                else
                {

                    CTPhieuBanDTO ctpbDTO = new CTPhieuBanDTO();
                    ctpbDTO.MaSP = reader.GetString(0);
                    ctpbDTO.SLBan = reader.GetInt32(1);
                    ctpbDTO.ThanhTien = reader.GetDouble(2);
                    DanhSach.Add(ctpbDTO);
                }
            }
            conn.Close();
            return DanhSach;
        }
        public List<CTPhieuBanDTO> LayThongTinCTBH(string MaBH)
        {
            string sql = "SELECT * FROM CTPHIEUBAN  WHERE MaBH = '" + MaBH + "'";
            OleDbConnection conn = new OleDbConnection();
            conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader reader = cmd.ExecuteReader();
            List<CTPhieuBanDTO> DanhSach = new List<CTPhieuBanDTO>();

            while (reader.Read())
            {
                CTPhieuBanDTO ctpbDTO = new CTPhieuBanDTO();
                ctpbDTO.MaCTPB = reader.GetString(0);
                ctpbDTO.MaBH = reader.GetString(1);
                ctpbDTO.MaSP = reader.GetString(2);
                ctpbDTO.MaKH = reader.GetString(3);
                ctpbDTO.SLBan = reader.GetInt32(4);
                ctpbDTO.DonGia = reader.GetInt32(5);
                ctpbDTO.ThanhTien = reader.GetDouble(6);
                DanhSach.Add(ctpbDTO);
            }
            conn.Close();
            return DanhSach;
        }
        
    }

}
